package cn.edu.fudan.autoparser;

import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.Map;
import java.util.Map.Entry;

import javax.lang.model.element.Parameterizable;
import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;
import javax.swing.text.DefaultEditorKit.InsertBreakAction;

import org.apache.commons.collections.map.StaticBucketMap;

import com.gargoylesoftware.htmlunit.javascript.host.Set;

public class ResultDatabase {
	private static Connection connection=null;
	private static PreparedStatement insertContentStatement=null;
	private static PreparedStatement insertTitleStatement=null;
	static{
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/Advertisement?useServerPrepStmts=false&"
					+ "rewriteBatchedStatements=true", "root", "root");
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
	
	static{
		String insertContentSQL="insert into result(urlid,word,num) values(?,?,?)";
		String insertTitleSQL="insert into title(urlid,word,num) values(?,?,?)";
		try {
			insertContentStatement=connection.prepareStatement(insertContentSQL);
			insertTitleStatement=connection.prepareStatement(insertTitleSQL);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	
	public static int insertTitle(String url,Map<String, Integer> numCounterTable){
		int updatedCounter=0;
		for(Entry<String, Integer> entry:numCounterTable.entrySet()){
			try {
				insertTitleStatement.setString(1, url);
				insertTitleStatement.setString(2, entry.getKey());
				insertTitleStatement.setInt(3, entry.getValue());
				insertTitleStatement.addBatch();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		try{
			updatedCounter=insertTitleStatement.executeBatch().length;
		}catch(SQLException e){
			e.printStackTrace();
		}
		return updatedCounter;
	}
	public static int insertContent(String url,Map<String, Integer> numCounterTable){
		int updatedCounter=0;
		for(Entry<String, Integer> entry:numCounterTable.entrySet()){
			try {
				insertContentStatement.setString(1, url);
				insertContentStatement.setString(2, entry.getKey());
				insertContentStatement.setInt(3, entry.getValue());
				insertContentStatement.addBatch();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		try{
			updatedCounter=insertContentStatement.executeBatch().length;
		}catch(SQLException e){
			e.printStackTrace();
		}
		return updatedCounter;
	}
	public static CachedRowSet getContentWords(String url) throws SQLException{
		String querySQL="select word,num from result where urlid=? order by num desc";
		CachedRowSet rowSet=null;
		try(
				PreparedStatement statement=connection.prepareStatement(querySQL);
				){
			statement.setString(1, url);
			ResultSet set=statement.executeQuery();
			RowSetFactory factory=RowSetProvider.newFactory();
			rowSet=factory.createCachedRowSet();
			rowSet.populate(set);
			set.close();
		}
		return rowSet;
		
	}
	public static CachedRowSet getTitleWords(String url) throws SQLException{
		String querySQL="select word,num from title where urlid=? order by num desc";
		CachedRowSet rowSet=null;
		try(
				PreparedStatement statement=connection.prepareStatement(querySQL);
				){
			statement.setString(1, url);
			ResultSet set=statement.executeQuery();
			RowSetFactory factory=RowSetProvider.newFactory();
			rowSet=factory.createCachedRowSet();
			rowSet.populate(set);
			set.close();
		}
		return rowSet;
		
	}
	public static CachedRowSet getContentWords(String[] urls) throws SQLException{
		String querySQL="select word,sum(num) from result where urlid in ? group by word order by sum(num) desc";
		CachedRowSet rowSet=null;
		try(
				PreparedStatement statement=connection.prepareStatement(querySQL);
				){
//			connection.createArrayOf("varchar", urls);
			statement.setArray(1, connection.createArrayOf("varchar(255)", urls));
			ResultSet set=statement.executeQuery();
			RowSetFactory factory=RowSetProvider.newFactory();
			rowSet=factory.createCachedRowSet();
			rowSet.populate(set);
			set.close();
		}
		return rowSet;
		
	}
	public static CachedRowSet getTitleWords(String[] urls) throws SQLException{
		String querySQL="select word,sum(num) from title where urlid in ? group by word order by sum(num) desc";
		CachedRowSet rowSet=null;
		try(
				PreparedStatement statement=connection.prepareStatement(querySQL);
				){
			statement.setArray(1, connection.createArrayOf("varchar(255)", urls));
			ResultSet set=statement.executeQuery();
			RowSetFactory factory=RowSetProvider.newFactory();
			rowSet=factory.createCachedRowSet();
			rowSet.populate(set);
			set.close();
		}
		return rowSet;
		
	}
}
